USE [MIS_GRUPO_07]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*************************************
		DIM TABLES
*************************************/

IF OBJECT_ID('dbo.DIM_Usuario', 'U') IS NOT NULL DROP TABLE dbo.DIM_Usuario
CREATE TABLE [dbo].[DIM_Usuario](
	[sk]				[int] IDENTITY(1,1) NOT NULL,
	[idUsuario]			[int] NULL,
	[sexo]				[char](1) NULL,
	[rangoEdad]			[char](10) NULL,
	[localidad]			[nvarchar](50) NULL,
	[provincia]			[nvarchar](50) NULL
) ON [PRIMARY]

IF OBJECT_ID('dbo.DIM_Estacion', 'U') IS NOT NULL DROP TABLE dbo.DIM_Estacion
CREATE TABLE [dbo].[DIM_Estacion](
	[sk]				[int] IDENTITY(1,1) NOT NULL,
	[estacionID]		[int] NULL,
	[estacionNombre]	[nvarchar](50) NULL,
   [latitud] [decimal](10,6) NULL,
    [longitud] [decimal](10,6) NULL,
	[barrio]			[nvarchar](50) NULL
) ON [PRIMARY]

IF OBJECT_ID('dbo.DIM_Tiempo', 'U') IS NOT NULL DROP TABLE dbo.DIM_Tiempo
CREATE TABLE [dbo].[DIM_Tiempo](
	[sk]	  [int]  NOT NULL,
  [anio]      [int] NULL,
  [mesDesc]   [char](20) NULL,
  [mesNro]    [int] NULL,
  [dia]       [int] NULL,
  [franjaHoraria]		[char](10) NULL,
	[hora]				[int] NULL
) ON [PRIMARY]

IF OBJECT_ID('dbo.DIM_TipoLluvia', 'U') IS NOT NULL DROP TABLE dbo.DIM_TipoLluvia
CREATE TABLE [dbo].[DIM_TipoLluvia](
	[sk]				[int] IDENTITY(1,1) NOT NULL,
	[tipoLluvia]		[char](10) NULL
) ON [PRIMARY]

IF OBJECT_ID('dbo.DIM_TipoTemperatura', 'U') IS NOT NULL DROP TABLE dbo.DIM_TipoTemperatura
CREATE TABLE [dbo].[DIM_TipoTemperatura](
	[sk]				[int] IDENTITY(1,1) NOT NULL,
	[tipoTemperatura]	[char](10) NULL
) ON [PRIMARY]

IF OBJECT_ID('dbo.DIM_TMI', 'U') IS NOT NULL DROP TABLE dbo.DIM_TMI
CREATE TABLE [dbo].[DIM_TMI](
	[sk]				[int] IDENTITY(1,1) NOT NULL,
	[idTMI]				[int] NULL,
	[direccion]			[nvarchar](100) NULL,
	[barrio]			[nvarchar](50) NULL
) ON [PRIMARY]


/*************************************
		BT TABLES
*************************************/

IF OBJECT_ID('dbo.BT_Recorrido', 'U') IS NOT NULL DROP TABLE dbo.BT_Recorrido
CREATE TABLE [dbo].[BT_Recorrido](
	[sk_tiempoOrigen] [int] NOT NULL,
	[sk_tiempoDestino] [int] NOT NULL,
	[sk_estacionOrigen] [int] NOT NULL,
	[sk_estacionDestino] [int] NOT NULL,
	[sk_usuario] [int] NOT NULL,
	[distanciaRecorrida] [decimal](10,4) NOT NULL,
	[tiempoUso] [int] NOT NULL,
	[cantidadUsos] [int] NOT NULL
) ON [PRIMARY]

IF OBJECT_ID('dbo.BT_Clima', 'U') IS NOT NULL DROP TABLE dbo.BT_Clima
CREATE TABLE [dbo].[BT_Clima](
	[sk_tiempoOrigen] [int] NOT NULL,
	[sk_estacionOrigen] [int] NOT NULL,
	[sk_TipoLluvia] [int] NOT NULL,
	[sk_TipoTemperatura] [int] NOT NULL,
	[sk_TMI] [nchar](10) NOT NULL,
	[temperatura] [int] NOT NULL,
	[lluvia] [int] NOT NULL
) ON [PRIMARY]


/*************************************
		AUX TABLES
*************************************/

IF OBJECT_ID('dbo.AUX_Coordinates_to_Barrio', 'U') IS NOT NULL DROP TABLE dbo.AUX_Coordinates_to_Barrio
CREATE TABLE [dbo].[AUX_Coordinates_to_Barrio](
	[cLat]				[decimal](10,6) NOT NULL,
	[cLon]				[decimal](10,6) NOT NULL,
	[barrio]			[nvarchar](50) NULL
) ON [PRIMARY]

IF OBJECT_ID('dbo.AUX_Rango_Edad', 'U') IS NOT NULL DROP TABLE dbo.AUX_Rango_Edad
CREATE TABLE [dbo].[AUX_Rango_Edad](
	[rangoEdad]		    [char](10) NOT NULL,
	[edadDesde]			[int] NOT NULL,
	[edadHasta]			[int] NOT NULL
) ON [PRIMARY]

IF OBJECT_ID('dbo.AUX_Tipo_Temperatura', 'U') IS NOT NULL DROP TABLE dbo.AUX_Tipo_Temperatura
CREATE TABLE [dbo].[AUX_Tipo_Temperatura](
	[tipoTemperatura]		    [char](10) NOT NULL,
	[tDesde]			[int] NOT NULL,
	[tHasta]			[int] NOT NULL
) ON [PRIMARY]

IF OBJECT_ID('dbo.AUX_Tipo_Lluvia', 'U') IS NOT NULL DROP TABLE dbo.AUX_Tipo_Lluvia
CREATE TABLE [dbo].[AUX_Tipo_Lluvia](
	[tipoLluvia]		    [char](10) NOT NULL,
	[mmDesde]			[int] NOT NULL,
	[mmHasta]			[int] NOT NULL
) ON [PRIMARY]


